Attribute VB_Name = "ModCountry"
'---------------------------------------------------------------------------------------
'Host Name: Shared.Country
'Module: ModCountry
'Author: Keith Thomas, Alpha King, Joseph Weir
'Date: 07/15/2003
'Version: 2005-03-31
'Description:
'Contains a functions to manipulate zip, postal, state and country codes.
'---------------------------------------------------------------------------------------

'---------------------------------------------------------------------------------------
'2004-11-01, JFW, Enhanced for Mervyn Kaplan
'2004-11-01, JFW, Added StateConv
'2005-03-30, JFW, Added Zip to State via Shipnet database (ODBC3)
'2005-04-06, JFW, ZipToCountry for CA, 6 character non-numeric.
'---------------------------------------------------------------------------------------

Option Explicit




Public Function CountryConv(ByVal strCountry As String) As String

    'Written by Lahcen Boualam on 7/15/03.
    'Modified by Keith Thomas on 7/15/3.
    'Modified by Bruce Acomb on 7/18/3.
    'Called from ODBC1.QueryTable.
    'This function converts the customer country code
    'to a CAFE-friendly country code.

    On Error GoTo EH

    Select Case UCase(Trim(strCountry))
        Case "CANADA", "CA", "CA"
            strCountry = "CA"          'CANADA
        Case "USA", "U.S.A.", "UNITED STATES", "", "US", "ETATS UNIS"
            strCountry = "US"          'UNITED STATES
            
        Case "ALBANIA", "AL"
            strCountry = "AL"          'ALBANIA
        Case "AMERICAN SAMOA", "AS"
            strCountry = "AS"          'AMERICAN SAMOA
        Case "ANDORRA", "AD"
            strCountry = "AD"          'ANDORRA
        Case "ANGOLA", "AO"
            strCountry = "AO"          'ANGOLA
        Case "ANGUILLA", "AI"
            strCountry = "AI"          'ANGUILLA
        Case "ANTIGUA", "AG"
            strCountry = "AG"          'ANTIGUA
        Case "ARGENTINA", "AR"
            strCountry = "AR"          'ARGENTINA
        Case "ARMENIA", "AM"
            strCountry = "AM"          'ARMENIA
        Case "ARUBA", "AW"
            strCountry = "AW"          'ARUBA
        Case "AUSTRIA", "AT"
            strCountry = "AT"          'AUSTRIA
        Case "AUSTRALIA", "AU", "AUSTRALIE"
            strCountry = "AU"          'AUSTRALIA
        Case "AZERBAIJAN", "AZ"
            strCountry = "AZ"          'AZERBAIJAN
        Case "BAHAMAS", "BS"
            strCountry = "BS"          'BAHAMAS
        Case "BAHRAIN", "BH"
            strCountry = "BH"          'BAHRAIN
        Case "BANGLADESH", "BD"
            strCountry = "BD"          'BANGLADESH
        Case "BARBADOS", "BB"
            strCountry = "BB"          'BARBADOS
        Case "BELARUS", "BY"
            strCountry = "BY"          'BELARUS
        Case "BELGIUM", "BE", "BELGIQUE"
            strCountry = "BE"          'BELGIUM
        Case "BELIZE", "BZ"
            strCountry = "BZ"          'BELIZE
        Case "BENIN", "BJ"
            strCountry = "BJ"          'BENIN
        Case "BERMUDA", "BM"
            strCountry = "BM"          'BERMUDA
        Case "BHUTAN", "BT"
            strCountry = "BT"          'BHUTAN
        Case "BOLIVIA", "BO"
            strCountry = "BO"          'BOLIVIA
        Case "BOSNIA-HERZEGOVINA", "BA"
            strCountry = "BA"          'BOSNIA-HERZEGOVINA
        Case "BOTSWANA", "BW"
            strCountry = "BW"          'BOTSWANA
        Case "BRAZIL", "BR"
            strCountry = "BR"          'BRAZIL
        Case "BRITISH VIRGIN ISLANDS", "VG"
            strCountry = "VG"          'BRITISH VIRGIN ISLANDS
        Case "BRUNEI", "BN"
            strCountry = "BN"          'BRUNEI
        Case "BULGARIA", "BG"
            strCountry = "BG"          'BULGARIA
        Case "BURKINA FASO", "BF"
            strCountry = "BF"          'BURKINA FASO
        Case "BURUNDI", "BI"
            strCountry = "BI"          'BURUNDI
        Case "CAMBODIA", "KH"
            strCountry = "KH"          'CAMBODIA
        Case "CAMEROON", "CM"
            strCountry = "CM"          'CAMEROON
        Case "CAPE VERDE", "CV"
            strCountry = "CV"          'CAPE VERDE
        Case "CAYMAN ISLANDS", "KY"
            strCountry = "KY"          'CAYMAN ISLANDS
        Case "CHAD", "TD"
            strCountry = "TD"          'CHAD
        Case "CHILE", "CL"
            strCountry = "CL"          'CHILE
        Case "CHINA", "CN"
            strCountry = "CN"          'CHINA
        Case "COLOMBIA", "CO"
            strCountry = "CO"          'COLOMBIA
        Case "CONGO", "CG"
            strCountry = "CG"          'CONGO
        Case "COOK ISLANDS", "CK"
            strCountry = "CK"          'COOK ISLANDS
        Case "COSTA RICA", "CR"
            strCountry = "CR"          'COSTA RICA
        Case "CROATIA", "HR"
            strCountry = "HR"          'CROATIA
        Case "CYPRUS", "CY"
            strCountry = "CY"          'CYPRUS
        Case "CZECH REPUBLIC", "CZ"
            strCountry = "CZ"          'CZECH REPUBLIC
        Case "DENMARK", "DK"
            strCountry = "DK"          'DENMARK
        Case "DEMOCRATIC REPUBLIC OF CONGO", "CD"
            strCountry = "CD"          'CONGO
        Case "DJIBOUTI", "DJ"
            strCountry = "DJ"          'DJIBOUTI
        Case "DOMINICA", "DM"
            strCountry = "DM"          'DOMINICA
        Case "DOMINICAN REPUBLIC", "DO"
            strCountry = "DO"          'DOMINICAN REPUBLIC
        Case "ECUADOR", "EC"
            strCountry = "EC"          'ECUADOR
        Case "EGYPT", "EG"
            strCountry = "EG"          'EGYPT
        Case "EL SALVADOR", "SV"
            strCountry = "SV"          'EL SALVADOR
        Case "England", "ANGLETERRE"
            strCountry = "GB"          'ENGLAND
        Case "EQUATORIAL GUINEA", "GQ"
            strCountry = "GQ"          'EQUATORIAL GUINEA
        Case "ERITREA", "ER"
            strCountry = "ER"          'ERITREA
        Case "ESTONIA", "EE"
            strCountry = "EE"          'ESTONIA
        Case "ETHIOPIA", "ET"
            strCountry = "ET"          'ETHIOPIA
        Case "FAEROE ISLANDS", "FO"
            strCountry = "FO"          'FAEROE ISLANDS
        Case "FINLAND", "FI"
            strCountry = "FI"          'FINLAND
        Case "FIJI", "FJ"
            strCountry = "FJ"          'FIJI
        Case "FRANCE", "FR"
            strCountry = "FR"          'FRANCE
        Case "FRENCH GUIANA", "GF"
            strCountry = "GF"          'FRENCH GUIANA
        Case "FRENCH POLYNESIA", "PF"
            strCountry = "PF"          'FRENCH POLYNESIA
        Case "GABON", "GA"
            strCountry = "GA"          'GABON
        Case "GAMBIA", "GM"
            strCountry = "GM"          'GAMBIA
        Case "GEORGIA", "GE"
            strCountry = "GE"          'GEORGIA
        Case "GERMANY", "DE", "ALLEMAGNE"
            strCountry = "DE"          'GERMANY
        Case "GHANA", "GH"
            strCountry = "GH"          'GHANA
        Case "GIBRALTAR", "GI"
            strCountry = "GI"          'GIBRALTAR
        Case "GREECE", "GR"
            strCountry = "GR"          'GREECE
        Case "GREENLAND", "GL"
            strCountry = "GL"          'GREENLAND
        Case "GRENADA", "GD"
            strCountry = "GD"          'GRENADA
        Case "GUADELOUPE", "GP"
            strCountry = "GP"          'GUADELOUPE
        Case "GUAM", "GU"
            strCountry = "GU"          'GUAM
        Case "GUATEMALA", "GT"
            strCountry = "GT"          'GUATEMALA
        Case "GUINEA", "GN"
            strCountry = "GN"          'GUINEA
        Case "GUYANA", "GY"
            strCountry = "GY"          'GUYANA
        Case "HAITI", "HT"
            strCountry = "HT"          'HAITI
         Case "HONDURAS", "HN"
            strCountry = "HN"          'HONDURAS
        Case "HONG KONG", "HK"
            strCountry = "HK"          'HONG KONG
        Case "HUNGARY", "HU"
            strCountry = "HU"          'HUNGARY
        Case "ICELAND", "IS"
            strCountry = "IS"          'ICELAND
        Case "INDIA", "IN"
            strCountry = "IN"          'INDIA
        Case "INDONESIA", "ID"
            strCountry = "ID"          'INDONESIA
        Case "IRELAND", "IE", "IRLANDE"
            strCountry = "IE"          'IRELAND
        Case "ISRAEL", "IL"
            strCountry = "IL"          'ISRAEL
        Case "ITALY", "IT", "ITALIE"
            strCountry = "IT"          'ITALY
        Case "IVORY COAST", "CI"
            strCountry = "CI"          'IVORY COAST
        Case "JAMAICA", "JM"
            strCountry = "JM"          'JAMAICA
        Case "JAPAN", "JP", "JAPON"
            strCountry = "JP"          'JAPAN
        Case "JORDAN", "JO"
            strCountry = "JO"          'JORDAN
        Case "KAZAKHSTAN", "KZ"
            strCountry = "KZ"          'KAZAKHSTAN
        Case "KENYA", "KE"
            strCountry = "KE"          'KENYA
        Case "KUWAIT", "KW"
            strCountry = "KW"          'KUWAIT
        Case "KYRGYZSTAN", "KG"
            strCountry = "KG"          'KYRGYZSTAN
        Case "LAOS", "LA"
            strCountry = "LA"          'LAOS
        Case "LATVIA", "LV"
            strCountry = "LV"          'LATVIA
        Case "LEBANON", "LB"
            strCountry = "LB"          'LEBANON
        Case "LESOTHO", "LS"
            strCountry = "LS"          'LESOTHO
        Case "LIBERIA", "LR"
            strCountry = "LR"          'LIBERIA
        Case "LIECHTENSTEIN", "LI"
            strCountry = "LI"          'LIECHTENSTEIN
        Case "LITHUANIA", "LT"
            strCountry = "LT"          'LITHUANIA
        Case "LUXEMBOURG", "LU"
            strCountry = "LU"          'LUXEMBOURG
        Case "MACAU", "MO"
            strCountry = "MO"          'MACAU
        Case "MACEDONIA", "MK"
            strCountry = "MK"          'MACEDONIA
        Case "MADAGASCAR", "MG"
            strCountry = "MG"          'MADAGASCAR
        Case "MALAWI", "MW"
            strCountry = "MW"          'MALAWI
        Case "MALAYSIA", "MY"
            strCountry = "MY"          'MALAYSIA
        Case "MALDIVES", "MV"
            strCountry = "MV"          'MALDIVES
        Case "MALI", "ML"
            strCountry = "ML"          'MALI
        Case "MALTA", "MT"
            strCountry = "MT"          'MALTA
        Case "MARSHALL ISLANDS", "MH"
            strCountry = "MH"          'MARSHALL ISLANDS
        Case "MARTINIQUE", "MQ"
            strCountry = "MQ"          'MARTINIQUE
        Case "MAURITANIA", "MR"
            strCountry = "MR"          'MAURITANIA
        Case "MAURITIUS", "MU"
            strCountry = "MU"          'MAURITIUS
        Case "MEXICO", "MX", "MEXIQUE"
            strCountry = "MX"          'MEXICO
        Case "MICRONESIA", "FM"
            strCountry = "FM"          'MICRONESIA
        Case "MOLDOVA", "MD"
            strCountry = "MD"          'MOLDOVA
        Case "MONACO", "MC"
            strCountry = "MC"          'MONACO
        Case "MONGOLIA", "MN"
            strCountry = "MN"          'MONGOLIA
        Case "MONTSERRAT", "MS"
            strCountry = "MS"          'MONTSERRAT
        Case "MOROCCO", "MA"
            strCountry = "MA"          'MOROCCO
        Case "MOZAMBIQUE", "MZ"
            strCountry = "MZ"          'MOZAMBIQUE
        Case "NAMIBIA", "NA"
            strCountry = "NA"          'NAMIBIA
        Case "NEPAL", "NP"
            strCountry = "NP"          'NEPAL
        Case "NETHERLANDS", "NL"
            strCountry = "NL"          'NETHERLANDS
        Case "NETHERLANDS ANTILLES", "AN"
            strCountry = "AN"          'NETHERLANDS ANTILLES
        Case "NEW CALEDONIA", "NC"
            strCountry = "NC"          'NEW CALEDONIA
        Case "NEW ZEALAND", "NZ"
            strCountry = "NZ"          'NEW ZEALAND
        Case "NICARAGUA", "NI"
            strCountry = "NI"          'NICARAGUA
        Case "NIGER", "NE"
            strCountry = "NE"          'NIGER
        Case "NIGERIA", "NG"
            strCountry = "NG"          'NIGERIA
        Case "NORWAY", "NO"
            strCountry = "NO"          'NORWAY
        Case "OMAN", "OM"
            strCountry = "OM"          'OMAN
        Case "PAKISTAN", "PK"
            strCountry = "PK"          'PAKISTAN
        Case "PALAU", "PW"
            strCountry = "PW"          'PALAU
        Case "PANAMA", "PA"
            strCountry = "PA"          'PANAMA
        Case "PAPUA NEW GUINEA", "PG"
            strCountry = "PG"          'PAPUA NEW GUINEA
        Case "PARAGUAY", "PY"
            strCountry = "PY"          'PARAGUAY
        Case "PERU", "PE"
            strCountry = "PE"          'PERU
        Case "PHILIPPINES", "PH"
            strCountry = "PH"          'PHILIPPINES
        Case "POLAND", "PL"
            strCountry = "PL"          'POLAND
        Case "PORTUGAL", "PT"
            strCountry = "PT"          'PORTUGAL
        Case "PUERTO RICO", "PR"
            strCountry = "PR"          'PUERTO RICO
        Case "QATAR", "QA"
            strCountry = "QA"          'QATAR
        Case "REUNION", "RE"
            strCountry = "RE"          'REUNION
        Case "ROMANIA", "RO"
            strCountry = "RO"          'ROMANIA
        Case "RUSSIA", "RU"
            strCountry = "RU"          'RUSSIA
        Case "RWANDA", "RW"
            strCountry = "RW"          'RWANDA
        Case "SAIPAN", "MP"
            strCountry = "MP"          'SAIPAN
        Case "SAUDI ARABIA", "SA"
            strCountry = "SA"          'SAUDI ARABIA
        Case "SENEGAL", "SN"
            strCountry = "SN"          'SENEGAL
        Case "SEYCHELLES", "SC"
            strCountry = "SC"          'SEYCHELLES
        Case "SINGAPORE", "SG"
            strCountry = "SG"          'SINGAPORE
        Case "SLOVAK REPUBLIC", "SK"
            strCountry = "SK"          'SLOVAK REPUBLIC
        Case "SLOVENIA", "SI"
            strCountry = "SI"          'SLOVENIA
        Case "SOUTH AFRICA", "ZA"
            strCountry = "ZA"          'SOUTH AFRICA
        Case "SOUTH KOREA", "KR"
            strCountry = "KR"          'SOUTH KOREA
        Case "SPAIN", "ES", "ESPAGNE"
            strCountry = "ES"          'SPAIN
        Case "SRI LANKA"
            strCountry = "LK"
        Case "ST. KITTS AND NEVIS", "KN"
            strCountry = "KN"          'ST. KITTS AND NEVIS
        Case "ST. LUCIA", "LC"
            strCountry = "LC"          'ST. LUCIA
        Case "ST. VINCENT", "VC"
            strCountry = "VC"          'ST. VINCENT
        Case "SURINAME", "SR"
            strCountry = "SR"          'SURINAME
        Case "SWAZILAND", "SZ"
            strCountry = "SZ"          'SWAZILAND
        Case "SWEDEN", "SE"
            strCountry = "SE"          'SWEDEN
        Case "SWITZERLAND", "CH", "SUISSE"
            strCountry = "CH"          'SWITZERLAND
        Case "SYRIA", "SY"
            strCountry = "SY"          'SYRIA
        Case "TAIWAN", "TW"
            strCountry = "TW"          'TAIWAN
        Case "TANZANIA", "TZ"
            strCountry = "TZ"          'TANZANIA
        Case "THAILAND", "TH"
            strCountry = "TH"          'THAILAND
        Case "TOGO", "TG"
            strCountry = "TG"          'TOGO
        Case "TRINIDAD AND TOBAGO", "TT"
            strCountry = "TT"          'TRINIDAD AND TOBAGO
        Case "TUNISIA", "TN"
            strCountry = "TN"          'TUNISIA
        Case "TURKEY", "TR"
            strCountry = "TR"          'TURKEY
        Case "TURKMENISTAN", "TM"
            strCountry = "TM"          'TURKMENISTAN
        Case "TURKS AND CAICOS ISLANDS", "TC"
            strCountry = "TC"          'TURKS AND CAICOS ISLANDS
        Case "UGANDA", "UG"
            strCountry = "UG"          'UGANDA
        Case "UKRAINE", "UA"
            strCountry = "UA"          'UKRAINE
        Case "UNITED ARAB EMIRATES", "AE"
            strCountry = "AE"          'UNITED ARAB EMIRATES
        Case "UNITED KINGDOM", "GB", "UK"
            strCountry = "GB"          'UNITED KINGDOM
        Case "URUGUAY", "UY"
            strCountry = "UY"          'URUGUAY
        Case "U S VIRGIN ISLANDS", "VI", "U.S. VIRGIN ISLANDS"
            strCountry = "VI"          'U S VIRGIN ISLANDS
        Case "UZBEKISTAN", "UZ"
            strCountry = "UZ"          'UZBEKISTAN
        Case "VANUATU", "VU"
            strCountry = "VU"          'VANUATU
        Case "VATICAN CITY", "VA"
            strCountry = "VA"          'VATICAN CITY
        Case "VENEZUELA", "VE"
            strCountry = "VE"          'VENEZUELA
        Case "VIETNAM", "VN"
            strCountry = "VN"          'VIETNAM
        Case "WALLIS & FUTUNA", "WF"
            strCountry = "WF"          'WALLIS & FUTUNA
        Case "YEMEN", "YE"
            strCountry = "YE"          'YEMEN
        Case "YUGOSLAVIA", "YU"
            strCountry = "YU"          'YUGOSLAVIA
        Case "ZAMBIA", "ZM"
            strCountry = "ZM"          'ZAMBIA
        Case "ZIMBABWE", "ZW"
            strCountry = "ZW"          'ZIMBABWE
        Case Else
            strCountry = strCountry
    End Select
       
    CountryConv = strCountry
    Exit Function
EH:
   MsgBox Err.Description, vbSystemModal, "ModCountry.CountryConv"
End Function


Public Function Digit(strData As String) As String
    ' Return only Numbers
    
    ' Author: Keith Thomas
    
    ' set Digit to ""
    ' find length of string
    ' loop through each character position and see if chr is a number
    ' if it is numeric character add to Digit varible
     
     Dim intLen As Integer
     Dim intCounter As Integer
     Dim strTemp As String
     Digit = ""
     intLen = Len(strData)
     
     If intLen = 0 Then Exit Function
            
     Do Until intCounter = intLen
        
        intCounter = intCounter + 1
        strTemp = Mid(strData, intCounter, 1)
        
        Select Case Asc(UCase(strTemp))
    
            Case Is < 48
                ' do nothing
            Case Is > 57
                ' do nothing
            Case Else
                Digit = Digit & strTemp
         End Select
        
      Loop

End Function


Public Function PhoneNumber(strData As String) As String
    ' Return only 10 digits if possible
    
    Dim strPhone As String
    Dim intLen As Integer
    Dim intCounter As Integer
    Dim strTemp As String
    
    strPhone = ""
    intLen = Len(strData)
    
    If intLen = 0 Then Exit Function
          
    For intCounter = 1 To intLen
        strTemp = Mid(strData, intCounter, 1)
        If Asc(UCase(strTemp)) >= 48 And Asc(UCase(strTemp)) <= 57 Then
            strPhone = strPhone & strTemp
        ElseIf Len(strPhone) >= 10 And UCase(strTemp) = "X" Then
            Exit For
        ElseIf Len(strPhone) >= 10 And UCase(strTemp) = "-" Then
            Exit For
        End If
    Next intCounter

    If Len(strPhone) = 11 And Left(strPhone, 1) = "1" Then strPhone = Right(strPhone, 10)
    If Len(strPhone) = 10 Then
        PhoneNumber = strPhone
    Else
        PhoneNumber = strData
    End If
    
End Function


Public Function StateConv(ByVal strState As String) As String

    'Written by Lahcen Boualam on 7/15/03.
    'Modified by Keith Thomas on 7/15/3.
    'Modified by Bruce Acomb on 7/18/3.
    'Called from ODBC1.QueryTable.
    'This function converts the customer country code
    'to a CAFE-friendly country code.

    On Error GoTo EH

    strState = UCase(strState)
    
    If 0 <> InStr(1, strState, UCase("Newfound")) Then
        StateConv = "NL"
    ElseIf 0 <> InStr(1, strState, UCase("Lab")) Then
        StateConv = "NL"
    ElseIf 0 <> InStr(1, strState, UCase("Nova")) Then
        StateConv = "NS"
    ElseIf 0 <> InStr(1, strState, UCase("Bruns")) Then
        StateConv = "NB"
    ElseIf 0 <> InStr(1, strState, UCase("Prince")) Then
        StateConv = "PE"
    ElseIf 0 <> InStr(1, strState, UCase("Quebec")) Then
        StateConv = "QC"
    ElseIf 0 <> InStr(1, strState, UCase("Ontar")) Then
        StateConv = "ON"
    ElseIf 0 <> InStr(1, strState, UCase("Manitoba")) Then
        StateConv = "MB"
    ElseIf 0 <> InStr(1, strState, UCase("Sask")) Then
        StateConv = "SK"
    ElseIf 0 <> InStr(1, strState, UCase("British")) Then
        StateConv = "BC"
    ElseIf 0 <> InStr(1, strState, UCase("B.C.")) Then
        StateConv = "BC"
    ElseIf 0 <> InStr(1, strState, UCase("ALABAMA")) Then StateConv = "AL"
    ElseIf 0 <> InStr(1, strState, UCase("ALASKA")) Then StateConv = "AK"
    ElseIf 0 <> InStr(1, strState, UCase("ARIZONA")) Then StateConv = "AZ"
    ElseIf 0 <> InStr(1, strState, UCase("ARKANSAS")) Then StateConv = "AR"
    ElseIf 0 <> InStr(1, strState, UCase("CALIFORNIA ")) Then StateConv = "CA"
    ElseIf 0 <> InStr(1, strState, UCase("COLORADO")) Then StateConv = "CO"
    ElseIf 0 <> InStr(1, strState, UCase("CONNECTICUT")) Then StateConv = "CT"
    ElseIf 0 <> InStr(1, strState, UCase("DELAWARE")) Then StateConv = "DE"
    ElseIf 0 <> InStr(1, strState, UCase("DISTRICT")) Then StateConv = "DC"
    ElseIf 0 <> InStr(1, strState, UCase("D.C.")) Then StateConv = "DC"
    ElseIf 0 <> InStr(1, strState, UCase("FLORIDA")) Then StateConv = "FL"
    ElseIf 0 <> InStr(1, strState, UCase("GEORGIA")) Then StateConv = "GA"
    ElseIf 0 <> InStr(1, strState, UCase("GUAM ")) Then StateConv = "GU"
    ElseIf 0 <> InStr(1, strState, UCase("HAWAII")) Then StateConv = "HI"
    ElseIf 0 <> InStr(1, strState, UCase("IDAHO")) Then StateConv = "ID"
    ElseIf 0 <> InStr(1, strState, UCase("ILLINOIS")) Then StateConv = "IL"
    ElseIf 0 <> InStr(1, strState, UCase("INDIANA")) Then StateConv = "IN"
    ElseIf 0 <> InStr(1, strState, UCase("IOWA")) Then StateConv = "IA"
    ElseIf 0 <> InStr(1, strState, UCase("KANSAS")) Then StateConv = "KS"
    ElseIf 0 <> InStr(1, strState, UCase("KENTUCKY")) Then StateConv = "KY"
    ElseIf 0 <> InStr(1, strState, UCase("LOUISIANA")) Then StateConv = "LA"
    ElseIf 0 <> InStr(1, strState, UCase("MAINE")) Then StateConv = "ME"
    ElseIf 0 <> InStr(1, strState, UCase("MARYLAND")) Then StateConv = "MD"
    ElseIf 0 <> InStr(1, strState, UCase("MASSACHUSETTS")) Then StateConv = "MA"
    ElseIf 0 <> InStr(1, strState, UCase("MICHIGAN")) Then StateConv = "MI"
    ElseIf 0 <> InStr(1, strState, UCase("MINNESOTA")) Then StateConv = "MN"
    ElseIf 0 <> InStr(1, strState, UCase("MISSISSIPPI")) Then StateConv = "MS"
    ElseIf 0 <> InStr(1, strState, UCase("MISSOURI")) Then StateConv = "MO"
    ElseIf 0 <> InStr(1, strState, UCase("MONTANA")) Then StateConv = "MT"
    ElseIf 0 <> InStr(1, strState, UCase("NEBRASKA")) Then StateConv = "NE"
    ElseIf 0 <> InStr(1, strState, UCase("NEVADA")) Then StateConv = "NV"
    ElseIf 0 <> InStr(1, strState, UCase("NEW HAMPSHIRE")) Then StateConv = "NH"
    ElseIf 0 <> InStr(1, strState, UCase("NEW JERSEY")) Then StateConv = "NJ"
    ElseIf 0 <> InStr(1, strState, UCase("NEW MEXICO")) Then StateConv = "NM"
    ElseIf 0 <> InStr(1, strState, UCase("NEW YORK")) Then StateConv = "NY"
    ElseIf 0 <> InStr(1, strState, UCase("NORTH CAROLINA")) Then StateConv = "NC"
    ElseIf 0 <> InStr(1, strState, UCase("NORTH DAKOTA")) Then StateConv = "ND"
    ElseIf 0 <> InStr(1, strState, UCase("OHIO")) Then StateConv = "OH"
    ElseIf 0 <> InStr(1, strState, UCase("OKLAHOMA")) Then StateConv = "OK"
    ElseIf 0 <> InStr(1, strState, UCase("OREGON")) Then StateConv = "OR"
    ElseIf 0 <> InStr(1, strState, UCase("PALAU")) Then StateConv = "PW"
    ElseIf 0 <> InStr(1, strState, UCase("PENNSYLVANIA")) Then StateConv = "PA"
    ElseIf 0 <> InStr(1, strState, UCase("PUERTO RICO")) Then StateConv = "PR"
    ElseIf 0 <> InStr(1, strState, UCase("RHODE ISLAND")) Then StateConv = "RI"
    ElseIf 0 <> InStr(1, strState, UCase("SOUTH CAROLINA")) Then StateConv = "SC"
    ElseIf 0 <> InStr(1, strState, UCase("SOUTH DAKOTA")) Then StateConv = "SD"
    ElseIf 0 <> InStr(1, strState, UCase("TENNESSEE")) Then StateConv = "TN"
    ElseIf 0 <> InStr(1, strState, UCase("TEXAS")) Then StateConv = "TX"
    ElseIf 0 <> InStr(1, strState, UCase("UTAH")) Then StateConv = "UT"
    ElseIf 0 <> InStr(1, strState, UCase("VERMONT")) Then StateConv = "VT"
    ElseIf 0 <> InStr(1, strState, UCase("VIRGIN ISLANDS")) Then StateConv = "VI"
    ElseIf 0 <> InStr(1, strState, UCase("VIRGINIA")) Then StateConv = "VA"
    ElseIf 0 <> InStr(1, strState, UCase("WASHINGTON")) Then StateConv = "WA"
    ElseIf 0 <> InStr(1, strState, UCase("WEST VIRGINIA")) Then StateConv = "WV"
    ElseIf 0 <> InStr(1, strState, UCase("WISCONSIN")) Then StateConv = "WI"
    ElseIf 0 <> InStr(1, strState, UCase("WYOMING")) Then StateConv = "WY"
    Else
        StateConv = strState
    End If
    
    Exit Function
EH:
    StateConv = ""
End Function
'



Public Function StringNullToBlank(aValue As Variant) As Variant

    On Error GoTo ErrorExit
    
    If IsNull(aValue) Then
        StringNullToBlank = ""
    Else
        StringNullToBlank = aValue
    End If
    Exit Function

ErrorExit:
    Err.Clear
    StringNullToBlank = ""
    
End Function
'


Public Function ZipConv(ByVal strCountry As String, ByVal strZipCode As String) As String

    'Written by Alpha S. King   on 02/13/2004.
    'Modified by                on 02/13/2004.
    'Called from ODBC1.QueryTable.
    'This function converts the customer zip code
    'to a CAFE-friendly zip code.

    On Error GoTo EH
    Dim intZipLen As Integer

    ' strZipCode = Digit(strZipCode)
    intZipLen = Len(strZipCode)
    
    Select Case strCountry
        Case "AU", "AT", "BG", "DK", "NL", "NO", "PH", "CH"
            '4 digit Postal Codes
            strZipCode = Digit(strZipCode)
            If intZipLen = 0 Then
                strZipCode = "1111"
            Else
                strZipCode = "0000" & strZipCode
                strZipCode = Right(strZipCode, 4)
            End If
        Case "BR", "FR", "DE", "ID", "IT", "MY", "MX", "SG", "ES", "SE", "TH"
            '5 digit Postal Codes
            strZipCode = Digit(strZipCode)
            If intZipLen = 0 Then
                strZipCode = "11111"
            Else
                strZipCode = "00000" & strZipCode
                strZipCode = Right(strZipCode, 5)
            End If
        Case "CN"
            '6 digit Postal Codes
            strZipCode = Digit(strZipCode)
            If intZipLen = 0 Then
                strZipCode = "111111"
            Else
                strZipCode = "000000" & strZipCode
                strZipCode = Right(strZipCode, 6)
            End If
        Case "JP"
            '7 digit Postal Codes
            strZipCode = Digit(strZipCode)
            If intZipLen = 0 Then
                strZipCode = "1111111"
            Else
                strZipCode = "0000000" & strZipCode
                strZipCode = Right(strZipCode, 7)
            End If
        Case "US"
            '5 or 9 digits
            strZipCode = Digit(strZipCode)
            If intZipLen = 0 Then
                strZipCode = "11111"
            ElseIf intZipLen <= 5 Then
                strZipCode = "00000" & strZipCode
                strZipCode = Right(strZipCode, 5)
            Else
                strZipCode = "000000000" & strZipCode
                strZipCode = Right(strZipCode, 9)
            End If
        Case Else
            strZipCode = strZipCode
    End Select
    
    ZipConv = UCase(strZipCode)
    
    Exit Function
EH:
   MsgBox Err.Description, vbSystemModal, "ZipConv"
End Function

'---------------------------------------------------------------------------------------
' ZipToCountry
' Get US or CA from zip code.
'---------------------------------------------------------------------------------------

Public Function ZipToCountry(ByVal strZipCode As String) As String

    If IsNumeric(strZipCode) Then
        If Len(strZipCode) = 5 Or Len(strZipCode) = 9 Then
            ZipToCountry = "US"
        End If
    Else
        If Not IsNumeric(strZipCode) And Len(strZipCode) = 6 Then
            ZipToCountry = "CA"
        End If
    End If

End Function

'---------------------------------------------------------------------------------------
' ZipToProvince
' Get province code from zip code.
'---------------------------------------------------------------------------------------

Public Function ZipToProvince(ByVal strZipCode As String) As String
    
    strZipCode = UCase(strZipCode)
    
    If Not IsNumeric(strZipCode) Then
        Select Case Left(strZipCode, 1)
        Case "A"
            ZipToProvince = "NL"
        Case "B"
            ZipToProvince = "NS"
        Case "C"
            ZipToProvince = "PE"
        Case "E"
            ZipToProvince = "NB"
        Case "G", "H", "J"
            ZipToProvince = "QC"
        Case "K", "L", "M", "N", "P"
            ZipToProvince = "ON"
        Case "R"
            ZipToProvince = "MB"
        Case "S"
            ZipToProvince = "SK"
        Case "T"
            ZipToProvince = "AB"
        Case "V"
            ZipToProvince = "BC"
        Case "X"
            ZipToProvince = "NT"
        Case "Y"
            ZipToProvince = "YK"
        End Select
    End If
    
    Exit Function
EH:
   MsgBox Err.Description, vbSystemModal, "ZipToProvince"
End Function


